import json
from datetime import datetime

import requests
from urllib.parse import urljoin

import pymysql

BASE_URL = 'http://198.10.76.67'
GET_DEPT_DONE_URL = urljoin(BASE_URL, '/manage/windowapply/getDeptDone')
GET_BYID_DETAIL = urljoin(BASE_URL,'/cttic-egov-dpm/hpmDeclarationMain/getById/')
GET_DECLARATION_DETAIL = urljoin(BASE_URL,'/cttic-egov-rbm/hpmMaritimeDeclaration/getDetail?windowApplyId=')
GET_BY_PRIMARYKEY = urljoin(BASE_URL,'/manage/windowapply/getByPrimaryKey/')

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36 Edg/131.0.0.0',
    'Accept': 'application/json, text/plain, */*',
    'Authorization': 'Bearer b9fcbf40-5fc3-4e3c-8173-e69beb507023'
}

cookies = {
    'access_token': 'eyJhbGciOiJIUzI1NiJ9.eyJpYXQiOjE3MzcxMDY1NTksImV4cCI6MTczNzEzNTM1OSwic3ViIjoie1xuICBcInVzZXJJZFwiIDogXCIzNzNcIixcbiAgXCJwYXNzd29yZFwiIDogXCIkMmEkMTAkQ2lvcHkxbVBRTFRaOW5TSUpRY0ZaLnlnLjZRQzIvQXdMd29rVDJKY2NEVmtqSGJJbjdQZlNcIixcbiAgXCJ1c2VyQ29kZVwiIDogXCJsaXl1Y2hlbmdcIixcbiAgXCJ1c2VyTmFtZUNuXCIgOiBcIuadjueOieaIkFwiLFxuICBcImNsaWVudElwXCIgOiBcIlwiLFxuICBcImFwcElkXCIgOiBcIlwiLFxuICBcImFwcENvZGVcIiA6IFwiQ1RUSUNcIixcbiAgXCJvZmZpY2VJZFwiIDogXCIxNFwiLFxuICBcInN5c1JvbGVJZFwiIDogbnVsbCxcbiAgXCJvZmZpY2VOYW1lXCIgOiBcIua3seWcs-a1t-S6i-WxgFwiLFxuICBcInJvbGVJZHNcIiA6IFsgXCIyNDVcIiwgXCIxNjdcIiwgXCIxMTZcIiwgXCIxMTNcIiwgXCI2MzVcIiwgXCI5MFwiLCBcIjg4XCIsIFwiMTEyXCIsIFwiMjE1XCIsIFwiMjgzXCIsIFwiNjM0XCIsIFwiMjQ2XCIsIFwiNjY0XCIsIFwiNjYyXCIsIFwiNjY3XCIsIFwiMTE1XCIsIFwiMTcyXCIsIFwiNjQxXCIsIFwiNjI0XCIgXSxcbiAgXCJwYXNzd29yZE1vZGlmeVRpbWVcIiA6IG51bGwsXG4gIFwiaXNQYXNzd29yZE1vZGlmeVwiIDogbnVsbCxcbiAgXCJ1c2VybmFtZVwiIDogXCJsaXl1Y2hlbmdcIixcbiAgXCJkZXB0T2ZmaWNlQ29kZVwiIDogbnVsbCxcbiAgXCJkZXB0T2ZmaWNlTmFtZVwiIDogbnVsbCxcbiAgXCJzZXR0bGVPZmZpY2VDb2RlXCIgOiBudWxsLFxuICBcInNldHRsZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUNvZGVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUlkXCIgOiBudWxsLFxuICBcIm9mZmljZUNvZGVcIiA6IFwiMTRcIixcbiAgXCJ1c2VyQ29va2llS2V5XCIgOiBcIjM3M19DVFRJQ1wiLFxuICBcImRhdGFBdXRob3JpdHlJZFwiIDogbnVsbCxcbiAgXCJwcmluY2lwYWxPcmdDb2RlXCIgOiBcIua3seWcs-a1t-S6i-WxgFwiLFxuICBcIm9mZmljZVR5cGVcIiA6IG51bGwsXG4gIFwibWFuYWdlVHlwZVwiIDogbnVsbCxcbiAgXCJsb2dpblRpbWVcIiA6IG51bGwsXG4gIFwic3lzdGVtUmF0ZVwiIDogbnVsbCxcbiAgXCJob21lQ3VycmVuY3lcIiA6IG51bGwsXG4gIFwib2ZmaWNlTWRtSWRcIiA6IG51bGwsXG4gIFwiY29ycE1kbUlkXCIgOiBudWxsLFxuICBcImNvcnBTaG9ydENvZGVcIiA6IG51bGwsXG4gIFwic3lzUm9sZUxpc3RcIiA6IG51bGwsXG4gIFwiZW5hYmxlZFwiIDogZmFsc2UsXG4gIFwiYXV0aG9yaXRpZXNcIiA6IFsgXSxcbiAgXCJhY2NvdW50Tm9uRXhwaXJlZFwiIDogZmFsc2UsXG4gIFwiYWNjb3VudE5vbkxvY2tlZFwiIDogZmFsc2UsXG4gIFwiY3JlZGVudGlhbHNOb25FeHBpcmVkXCIgOiBmYWxzZVxufSJ9.x9sSpTr08gxz6e0thM2KEF0Bu50UwrnuVMgna89gBWI',
    'msa_token': 'b9fcbf40-5fc3-4e3c-8173-e69beb507023'
}

db = pymysql.connect(host='localhost',user='root',passwd='root@2024',db='ywdb',port=3366, charset='utf8')
cursor = db.cursor()

session = requests.Session()
session.headers.update(headers)


def insertData(datas):
    table = 'dep_business'
    # keys = ','.join(datas.keys())
    keys = 'windowApplyId, affairId, applyNum, applicant, applyDepartment, applyDate, applyStatus, applyStatusName, projectType, projectName, mechanism, userName, phoneNum, modelType, content, affairNum, affairName, legalDate, mechanismName, parentWindowApplyId, entrustStatus, shipRecord, windowApplyIdList, legalStartDate, applyStartTime, applyOfficerInfo, isImportantProfit, sendCertificate, guaranteeSlipInfo, tradeFlag, processStatus, toDoTaskUser, recordListDtos, mqConsumerStatus, isSailingWarningInfo, sbmShipBuildDate, serveTop, applicantList, modityDate, certCheckList, excelEntity, applyNumList, operationName, sbmRsSiteInspectionvo, entrust, shippingCompanyApplyDetail, vvesselName, acceptList, phone, organizationType, commitType, entrustCorrection, isMajor, approveOrgName, existChange, sbmRsApply, code, rowColor, applicationFileInfoDtoList, col1, otherOrgCode, receiveAddress, approveOrgCode, createDate, applyType'
    values = ','.join(['%s'] * 65)
    sql = 'insert into {table}({keys}) values ({values})'.format(table=table, keys=keys, values=values)
    print(sql)

    try:
        if cursor.execute(sql, (
        datas['windowApplyId'], datas['affairId'], datas['applyNum'], datas['applicant'], datas['applyDepartment'],
        datas['applyDate'], datas['applyStatus'], datas['applyStatusName'], datas['projectType'], datas['projectName'],
        datas['mechanism'], datas['userName'], datas['phoneNum'], datas['modelType'], datas['content'],
        datas['affairNum'], datas['affairName'], datas['legalDate'], datas['mechanismName'],
        datas['parentWindowApplyId'], datas['entrustStatus'], datas['shipRecord'], datas['windowApplyIdList'],
        datas['legalStartDate'], datas['applyStartTime'], datas['applyOfficerInfo'], datas['isImportantProfit'],
        datas['sendCertificate'], datas['guaranteeSlipInfo'], datas['tradeFlag'], datas['processStatus'],
        datas['toDoTaskUser'], datas['recordListDtos'], datas['mqConsumerStatus'], datas['isSailingWarningInfo'],
        datas['sbmShipBuildDate'], datas['serveTop'], datas['applicantList'], datas['modityDate'],
        datas['certCheckList'], datas['excelEntity'], datas['applyNumList'], datas['operationName'],
        datas['sbmRsSiteInspectionvo'], datas['entrust'], datas['shippingCompanyApplyDetail'], datas['vvesselName'],
        datas['acceptList'], datas['phone'], datas['organizationType'], datas['commitType'], datas['entrustCorrection'],
        datas['isMajor'], datas['approveOrgName'], datas['existChange'], datas['sbmRsApply'], datas['code'],
        datas['rowColor'], datas['applicationFileInfoDtoList'], datas['col1'], datas['otherOrgCode'],
        datas['receiveAddress'], datas['approveOrgCode'], datas['createDate'], datas['applyType'])):
            print(f'{datetime} insert success', datetime.now().strftime("%Y%m%d-%H%M%S"))
            db.commit()
    except Exception as e:
        print(f'insert fail::{e}')
        db.rollback()
        pass

def insertAAData(datas,table) :
    try:
        keys = ','.join(datas.keys())
        values = ','.join(['%s'] * len(datas))
        sql = 'insert into {table}({keys}) values ({values})'.format(table=table, keys=keys, values=values)
        print(sql)
        if cursor.execute(sql, tuple(datas.values())) :
            print(f'{datetime} insert success',datetime.now().strftime("%Y%m%d-%H%M%S"))
            db.commit()
    except Exception as e:
        print(f'insert fail::{e}')
        db.rollback()
        pass

for index in range(0,27):
    params = {'applyStatus': '06',
              'applyStartTime': '2024-09-01',
              'applyEndTime': '2024-12-31',
              'size': '1000',
              'isCheck': '1',
              'current': index+1}
    response_lo = session.get(GET_DEPT_DONE_URL, params=params, cookies=cookies)
    #data = response_lo.json()
    #print(data)

    html_content = response_lo.text
    data = json.loads(response_lo.text)
    value = data['data']['records']

# filenametime = datetime.now().strftime("%Y%m%d-%H%M%S")

    # for item in value:
    #     insertData(item)

    for row1 in  value:
        windowApplyId = row1['windowApplyId']
        # response2 = session.get(GET_BYID_DETAIL+windowApplyId, cookies=cookies)
        # if response2.text is not None:
        #     try:
        #         datas2 = json.loads(response2.text)
        #         if datas2 is not None:
        #             ifsuccess = datas2['success']
        #             if ifsuccess is True:
        #                 accept = datas2['date']['accept']
        #                 insertAAData(accept,'dep_accept')
        #
        #                 approveVo = datas2['date']['approveVo']
        #                 insertAAData(approveVo,'dep_approve')
        #                 # data2.append(approveVo)
        #                 # windowApply = datas2['date']['windowApply']
        #                 # df3 = pd.DataFrame(windowApply)
        #                 # df3.to_excel(filename3, index=[0])
        #     except json.decoder.JSONDecodeError as e:
        #         print("Error: Failed to decode json")
        #         pass

        # response3 = session.get(GET_DECLARATION_DETAIL+windowApplyId, cookies=cookies)
        # if response3.text is not None:
        #     try:
        #         datas3 = json.loads(response3.text)
        #         if datas3 is not None:
        #             ifsuccess = datas3['success']
        #             if ifsuccess is True:
        #                 print(datas3)
        #                 accept = datas3['data']['acceptDTO']
        #                 insertAAData(accept,'dep_accept')
        #
        #                 approveVo = datas3['data']['approveDTO']
        #                 insertAAData(approveVo,'dep_approve')
        #     except json.decoder.JSONDecodeError as e:
        #         print("Error: Failed to decode json")
        #         pass

        response4 = session.get(GET_BY_PRIMARYKEY + windowApplyId, cookies=cookies)
        if response4.text is not None:
            try:
                datas4 = json.loads(response4.text)
                if datas4 is not None:
                    ifsuccess = datas4['msg']
                    if ifsuccess == "success":
                        print(datas4)
                        accept = datas4['data']['accept']
                        insertAAData(accept, 'dep_accept')

                        approveVo = datas4['data']['approveVo']
                        insertAAData(approveVo, 'dep_approve')
            except json.decoder.JSONDecodeError as e:
                print("Error: Failed to decode json")
                pass

db.close()

print('插入完成！')
